#pragma once

#include <vector>
#include <string>

#include "../comm/log.hpp"
#include "../comm/util.hpp"
#include <mysql/mysql.h>

namespace ns_model
{
    using namespace std;
    using namespace ns_log;
    using namespace ns_util;

    struct Question
    {
        string number; // 题目编号
        string title;  // 标题
        string star;   // 题目难度
        string desc;   // 题目描述
        string head;   // 代码模板
        string test_input;   // 测试用例的输入
        string test_output;   // 测试用例的输出
        int cpu_limit; // 时间限制
        int mem_limit; // 空间限制
    };

    // 论坛帖子结构
    struct ForumPost
    {
        int id;
        int question_number;
        string user_id;  // 改为string类型，因为现在使用username作为外键
        string title;
        string content;
        string create_time;
        string update_time;
    };

    // 论坛评论结构
    struct ForumComment
    {
        int id;
        int post_id;
        string user_id;  // 改为string类型，因为现在使用username作为外键
        string content;
        string create_time;
    };

    const string oj_questions = "oj_questions";
    const string host = "localhost";
    const string user = "root";
    const string passwd = " ";
    const string db = "db_user";
    const int port = 3333;

    MYSQL *Connect()
    {
        MYSQL *my = mysql_init(nullptr);
        if (nullptr == mysql_real_connect(my, host.c_str(), user.c_str(),
                           passwd.c_str(), db.c_str(), port, nullptr, 0))
        {
            LOG(ERROR) << "数据库连接失败: " << mysql_error(my) << endl;
            mysql_close(my);
            return nullptr;
        }
        mysql_set_character_set(my, "utf8");
        return my;
    }

    class Model
    {
    private:
        bool QueryMySql(const string &sql, vector<Question> *out)
        {
            MYSQL *my = mysql_init(NULL);
            if (nullptr == mysql_real_connect(my, host.c_str(), user.c_str(), passwd.c_str(), db.c_str(), port, NULL, 0))
            {
                LOG(FALSE) << "数据库加载失败" << endl;
                return false;
            }
            mysql_set_character_set(my, "utf8");

            if (0 != mysql_query(my, sql.c_str()))
            {
                LOG(FALSE) << "sql执行出错" << endl;
                return false;
            }

            MYSQL_RES *res = mysql_store_result(my);
            int rows = mysql_num_rows(res);

            Question q;

            for (int i = 0; i < rows; i++)
            {
                MYSQL_ROW row = mysql_fetch_row(res);
                q.number = row[0];
                q.title = row[1];
                q.star = row[2];
                q.desc = row[3];
                q.head = row[4];
                q.test_input=row[5];
                q.test_output=row[6];
                q.cpu_limit = atoi(row[7]);
                q.mem_limit = atoi(row[8]);

                out->push_back(q);
            }

            mysql_close(my);

            return true;
        }

    public:
        bool GetAllQuestions(vector<Question> *out)
        {
            string sql = "select number, title, star, `desc`, head, test_input,test_output, cpu_limit, mem_limit from ";
            sql += oj_questions;

            return QueryMySql(sql, out);
        }
        bool GetOneQuestion(const string &number, Question *q)
        {
            string sql = "select number, title, star, `desc`, head, test_input,test_output, cpu_limit, mem_limit from ";
            sql += oj_questions;
            sql += " where number=";
            sql += number;
            vector<Question> out;
            if (QueryMySql(sql, &out))
            {
                if (out.size() == 1)
                {
                    *q = out[0];
                    return true;
                }
            }
            return false;
        }

    public:
        bool GetQuestionsByTitle(const string &keyword, vector<Question> *out)
        {
            MYSQL *my = Connect();
            if (my == nullptr)
            {
                LOG(ERROR) << "连接数据库失败" << endl;
                return false;
            }

            // 防SQL注入，使用更大的缓冲区
            char escaped[2048] = {0};
            mysql_real_escape_string(my, escaped, keyword.c_str(), keyword.length());

            string sql = "SELECT number, title, star, `desc`,head, test_input,test_output, cpu_limit, mem_limit FROM oj_questions WHERE title LIKE '%";
            sql += escaped;
            sql += "%'";

            return QueryMySql(sql, out);
        }

        bool AddQuestion(const Question &q)
        {
            MYSQL *my = Connect();
            if (my == nullptr)
            {
                LOG(ERROR) << "连接数据库失败" << endl;
                return false;
            }

            // 防SQL注入，使用更大的缓冲区
            char escaped_title[2048] = {0};
            char escaped_star[2048] = {0};
            char escaped_desc[4096] = {0};
            char escaped_head[4096] = {0};
            char escaped_test_input[4096] = {0};
            char escaped_test_output[4096] = {0};

            mysql_real_escape_string(my, escaped_title, q.title.c_str(), q.title.length());
            mysql_real_escape_string(my, escaped_star, q.star.c_str(), q.star.length());
            mysql_real_escape_string(my, escaped_desc, q.desc.c_str(), q.desc.length());
            mysql_real_escape_string(my, escaped_head, q.head.c_str(), q.head.length());
            mysql_real_escape_string(my, escaped_test_input, q.test_input.c_str(), q.test_input.length());
            mysql_real_escape_string(my, escaped_test_output, q.test_output.c_str(), q.test_output.length());

            string sql = "INSERT INTO " + oj_questions + 
                        " (number, title, star, `desc`, head, test_input,test_output, cpu_limit, mem_limit) VALUES ('" +
                        q.number + "', '" + escaped_title + "', '" + escaped_star + "', '" + 
                        escaped_desc + "', '" + escaped_head + "', '" + escaped_test_input + "', '"+ escaped_test_output + "', " +
                        to_string(q.cpu_limit) + ", " + to_string(q.mem_limit) + ")";

            if (mysql_query(my, sql.c_str()) != 0)
            {
                LOG(ERROR) << "插入题目失败: " << mysql_error(my) << endl;
                mysql_close(my);
                return false;
            }

            mysql_close(my);
            return true;
        }

        bool UpdateQuestion(const Question &q)
        {
            MYSQL *my = Connect();
            if (my == nullptr)
            {
                LOG(ERROR) << "连接数据库失败" << endl;
                return false;
            }

            // 防SQL注入，使用更大的缓冲区
            char escaped_title[2048] = {0};
            char escaped_star[2048] = {0};
            char escaped_desc[4096] = {0};
            char escaped_head[4096] = {0};
            char escaped_test_input[4096] = {0};
            char escaped_test_output[4096] = {0};

            mysql_real_escape_string(my, escaped_title, q.title.c_str(), q.title.length());
            mysql_real_escape_string(my, escaped_star, q.star.c_str(), q.star.length());
            mysql_real_escape_string(my, escaped_desc, q.desc.c_str(), q.desc.length());
            mysql_real_escape_string(my, escaped_head, q.head.c_str(), q.head.length());
            mysql_real_escape_string(my, escaped_test_input, q.test_input.c_str(), q.test_input.length());
            mysql_real_escape_string(my, escaped_test_output, q.test_output.c_str(), q.test_output.length());

            string sql = "UPDATE " + oj_questions + " SET title='" + escaped_title + 
                        "', star='" + escaped_star + 
                        "', `desc`='" + escaped_desc + 
                        "', head='" + escaped_head + 
                        "', test_input='" + escaped_test_input + 
                        "', test_output='" + escaped_test_output + 
                        "', cpu_limit=" + to_string(q.cpu_limit) + 
                        ", mem_limit=" + to_string(q.mem_limit) + 
                        " WHERE number='" + q.number + "'";

            if (mysql_query(my, sql.c_str()) != 0)
            {
                LOG(ERROR) << "更新题目失败: " << mysql_error(my) << endl;
                mysql_close(my);
                return false;
            }

            mysql_close(my);
            return true;
        }

        bool DeleteQuestion(const string &number)
        {
            MYSQL *my = Connect();
            if (my == nullptr)
            {
                LOG(ERROR) << "连接数据库失败" << endl;
                return false;
            }

            // 防SQL注入
            char escaped_number[64] = {0};
            mysql_real_escape_string(my, escaped_number, number.c_str(), number.length());

            string sql = "DELETE FROM " + oj_questions + " WHERE number='" + escaped_number + "'";

            if (mysql_query(my, sql.c_str()) != 0)
            {
                LOG(ERROR) << "删除题目失败: " << mysql_error(my) << endl;
                mysql_close(my);
                return false;
            }

            mysql_close(my);
            return true;
        }

        bool IsAdmin(const string &username, const string &password)
        {
            MYSQL *my = Connect();
            if (my == nullptr)
            {
                LOG(ERROR) << "连接数据库失败" << endl;
                return false;
            }

            // 防SQL注入，使用更大的缓冲区
            char escaped_username[2048] = {0};
            char escaped_password[2048] = {0};
            mysql_real_escape_string(my, escaped_username, username.c_str(), username.length());
            mysql_real_escape_string(my, escaped_password, password.c_str(), password.length());

            string sql = "SELECT * FROM admin_users WHERE username='";
            sql += escaped_username;
            sql += "' AND password='";
            sql += escaped_password;
            sql += "'";
            
            if (mysql_query(my, sql.c_str()) != 0)
            {
                LOG(ERROR) << "查询管理员失败: " << mysql_error(my) << endl;
                mysql_close(my);
                return false;
            }

            MYSQL_RES *res = mysql_store_result(my);
            if (res == nullptr)
            {
                mysql_close(my);
                return false;
            }

            bool is_admin = (mysql_num_rows(res) > 0);
            mysql_free_result(res);
            mysql_close(my);
            return is_admin;
        }

        // 获取题目的所有帖子
        bool GetForumPosts(const string &question_number, vector<ForumPost> *out)
        {
            MYSQL *my = Connect();
            if (my == nullptr)
            {
                LOG(ERROR) << "连接数据库失败" << endl;
                return false;
            }

            string sql = "SELECT p.id, p.question_number, p.user_id, p.title, p.content, "
                        "p.create_time, p.update_time "
                        "FROM forum_posts p "
                        "WHERE p.question_number = " + question_number + " "
                        "ORDER BY p.create_time DESC";

            if (mysql_query(my, sql.c_str()) != 0)
            {
                LOG(ERROR) << "查询帖子失败: " << mysql_error(my) << endl;
                mysql_close(my);
                return false;
            }

            MYSQL_RES *res = mysql_store_result(my);
            if (res == nullptr)
            {
                mysql_close(my);
                return false;
            }

            int rows = mysql_num_rows(res);
            for (int i = 0; i < rows; i++)
            {
                MYSQL_ROW row = mysql_fetch_row(res);
                ForumPost post;
                post.id = atoi(row[0]);
                post.question_number = atoi(row[1]);
                post.user_id = row[2] ? row[2] : "";
                post.title = row[3] ? row[3] : "";
                post.content = row[4] ? row[4] : "";
                post.create_time = row[5] ? row[5] : "";
                post.update_time = row[6] ? row[6] : "";
                out->push_back(post);
            }

            mysql_free_result(res);
            mysql_close(my);
            return true;
        }

        // 获取帖子的所有评论
        bool GetForumComments(int post_id, vector<ForumComment> *out)
        {
            MYSQL *my = Connect();
            if (my == nullptr)
            {
                LOG(ERROR) << "连接数据库失败" << endl;
                return false;
            }

            string sql = "SELECT c.id, c.post_id, c.user_id, c.content, c.create_time "
                        "FROM forum_comments c "
                        "WHERE c.post_id = " + to_string(post_id) + " "
                        "ORDER BY c.create_time ASC";

            if (mysql_query(my, sql.c_str()) != 0)
            {
                LOG(ERROR) << "查询评论失败: " << mysql_error(my) << endl;
                mysql_close(my);
                return false;
            }

            MYSQL_RES *res = mysql_store_result(my);
            if (res == nullptr)
            {
                mysql_close(my);
                return false;
            }

            int rows = mysql_num_rows(res);
            for (int i = 0; i < rows; i++)
            {
                MYSQL_ROW row = mysql_fetch_row(res);
                ForumComment comment;
                comment.id = atoi(row[0]);
                comment.post_id = atoi(row[1]);
                comment.user_id = row[2] ? row[2] : "";
                comment.content = row[3] ? row[3] : "";
                comment.create_time = row[4] ? row[4] : "";
                out->push_back(comment);
            }

            mysql_free_result(res);
            mysql_close(my);
            return true;
        }

        // 添加论坛帖子
        bool AddForumPost(const ForumPost &post)
        {
            MYSQL *my = Connect();
            if (my == nullptr)
            {
                LOG(ERROR) << "连接数据库失败" << endl;
                return false;
            }

            // 首先验证用户是否存在
            char escaped_user_id[2048] = {0};
            mysql_real_escape_string(my, escaped_user_id, post.user_id.c_str(), post.user_id.length());

            string check_sql = "SELECT username FROM oj_user WHERE username='";
            check_sql += escaped_user_id;
            check_sql += "'";

            if (mysql_query(my, check_sql.c_str()) != 0)
            {
                LOG(ERROR) << "验证用户失败: " << mysql_error(my) << endl;
                mysql_close(my);
                return false;
            }

            MYSQL_RES *check_res = mysql_store_result(my);
            if (check_res == nullptr || mysql_num_rows(check_res) != 1)
            {
                LOG(ERROR) << "用户不存在: " << post.user_id << endl;
                if (check_res) mysql_free_result(check_res);
                mysql_close(my);
                return false;
            }
            mysql_free_result(check_res);

            // 防SQL注入
            char escaped_title[2048] = {0};
            char escaped_content[4096] = {0};
            
            mysql_real_escape_string(my, escaped_title, post.title.c_str(), post.title.length());
            mysql_real_escape_string(my, escaped_content, post.content.c_str(), post.content.length());

            string sql = "INSERT INTO forum_posts (question_number, user_id, title, content) "
                        "VALUES (" + to_string(post.question_number) + ", '" + 
                        escaped_user_id + "', '" + escaped_title + "', '" + escaped_content + "')";

            LOG(INFO) << "执行SQL: " << sql << endl;

            if (mysql_query(my, sql.c_str()) != 0)
            {
                LOG(ERROR) << "添加帖子失败: " << mysql_error(my) << endl;
                mysql_close(my);
                return false;
            }

            mysql_close(my);
            return true;
        }

        // 添加论坛评论
        bool AddForumComment(const ForumComment &comment)
        {
            MYSQL *my = Connect();
            if (my == nullptr)
            {
                LOG(ERROR) << "连接数据库失败" << endl;
                return false;
            }

            // 防SQL注入
            char escaped_user_id[2048] = {0};
            char escaped_content[4096] = {0};
            
            mysql_real_escape_string(my, escaped_user_id, comment.user_id.c_str(), comment.user_id.length());
            mysql_real_escape_string(my, escaped_content, comment.content.c_str(), comment.content.length());

            string sql = "INSERT INTO forum_comments (post_id, user_id, content) "
                        "VALUES (" + to_string(comment.post_id) + ", '" + 
                        escaped_user_id + "', '" + escaped_content + "')";

            if (mysql_query(my, sql.c_str()) != 0)
            {
                LOG(ERROR) << "添加评论失败: " << mysql_error(my) << endl;
                mysql_close(my);
                return false;
            }

            mysql_close(my);
            return true;
        }

        // 获取单个帖子详情
        bool GetForumPost(int post_id, ForumPost *post)
        {
            MYSQL *my = Connect();
            if (my == nullptr)
            {
                LOG(ERROR) << "连接数据库失败" << endl;
                return false;
            }

            string sql = "SELECT p.id, p.question_number, p.user_id, p.title, p.content, "
                        "p.create_time, p.update_time "
                        "FROM forum_posts p "
                        "WHERE p.id = " + to_string(post_id);

            if (mysql_query(my, sql.c_str()) != 0)
            {
                LOG(ERROR) << "查询帖子失败: " << mysql_error(my) << endl;
                mysql_close(my);
                return false;
            }

            MYSQL_RES *res = mysql_store_result(my);
            if (res == nullptr)
            {
                mysql_close(my);
                return false;
            }

            if (mysql_num_rows(res) != 1)
            {
                mysql_free_result(res);
                mysql_close(my);
                return false;
            }

            MYSQL_ROW row = mysql_fetch_row(res);
            post->id = atoi(row[0]);
            post->question_number = atoi(row[1]);
            post->user_id = row[2] ? row[2] : "";
            post->title = row[3] ? row[3] : "";
            post->content = row[4] ? row[4] : "";
            post->create_time = row[5] ? row[5] : "";
            post->update_time = row[6] ? row[6] : "";

            mysql_free_result(res);
            mysql_close(my);
            return true;
        }

        bool AddSubmission(const string &user_id, const string &question_id, const string &status)
        {
            MYSQL *my = Connect();
            if (my == nullptr)
            {
                LOG(ERROR) << "连接数据库失败" << endl;
                return false;
            }


            // 防SQL注入
            char escaped_user_id[2048] = {0};
            char escaped_question_id[2048] = {0};
            char escaped_status[2048] = {0};
            mysql_real_escape_string(my, escaped_user_id, user_id.c_str(), user_id.length());
            mysql_real_escape_string(my, escaped_question_id, question_id.c_str(), question_id.length());
            mysql_real_escape_string(my, escaped_status, status.c_str(), status.length());

            string sql = "INSERT INTO oj_submissions (user_id, question_id, status, submit_time) VALUES ('";
            sql += escaped_user_id;
            sql += "', '";
            sql += escaped_question_id;
            sql += "', '";
            sql += escaped_status;
            sql += "', NOW())";


            if (mysql_query(my, sql.c_str()) != 0)
            {
                LOG(ERROR) << "插入提交记录失败: " << mysql_error(my) << endl;
                mysql_close(my);
                return false;
            }

            mysql_close(my);
            return true;
        }

    private:
        MYSQL *GetConnection()
        {
            static MYSQL my_conn;
            static std::once_flag flag;
            std::call_once(flag, [&]()
                           {
                mysql_init(&my_conn);
                mysql_real_connect(&my_conn, host.c_str(), user.c_str(), 
                                 passwd.c_str(), db.c_str(), port, NULL, 0); });
            return &my_conn;
        }
    };

    class UserModel
    {
    private:
    public:
        bool Register(const string &username, const string &password)
        {
            MYSQL *my = Connect();
            if (my == nullptr)
            {
                LOG(ERROR) << "连接数据库失败" << endl;
                return false;
            }

            // 防SQL注入，使用更大的缓冲区
            char escaped_username[2048] = {0};
            char escaped_password[2048] = {0};
            mysql_real_escape_string(my, escaped_username, username.c_str(), username.length());
            mysql_real_escape_string(my, escaped_password, password.c_str(), password.length());

            // 使用string构建SQL语句，避免缓冲区大小问题
            string sql = "INSERT INTO oj_user (username, password) VALUES ('";
            sql += escaped_username;
            sql += "','";
            sql += escaped_password;
            sql += "')";

            bool ret = (0 == mysql_query(my, sql.c_str()));
            mysql_close(my);
            return ret;
        }

        bool Login(const string &username, const string &password)
        {
            MYSQL *my = Connect();
            if (my == nullptr)
            {
                LOG(ERROR) << "连接数据库失败" << endl;
                return false;
            }

            // 防SQL注入，使用更大的缓冲区
            char escaped_username[2048] = {0};
            char escaped_password[2048] = {0};
            mysql_real_escape_string(my, escaped_username, username.c_str(), username.length());
            mysql_real_escape_string(my, escaped_password, password.c_str(), password.length());

            // 使用string构建SQL语句，避免缓冲区大小问题
            string sql = "SELECT password FROM oj_user WHERE username='";
            sql += escaped_username;
            sql += "'";

            if (mysql_query(my, sql.c_str()) != 0)
            {
                LOG(ERROR) << "查询用户失败: " << mysql_error(my) << endl;
                mysql_close(my);
                return false;
            }

            MYSQL_RES *res = mysql_store_result(my);
            if (res == nullptr)
            {
                mysql_close(my);
                return false;
            }

            if (mysql_num_rows(res) != 1)
            {
                mysql_free_result(res);
                mysql_close(my);
                return false;
            }

            MYSQL_ROW row = mysql_fetch_row(res);
            bool match = (password == row[0]);
            mysql_free_result(res);
            mysql_close(my);
            return match;
        }

        struct Submission
        {
            string number;
            string title;
            string star;
            string status;
            string submit_time; // 新增时间字段
        };

        vector<Submission> GetSubmissions(string user_id)
        {
            MYSQL *my = Connect();
            vector<Submission> submissions;
            mysql_set_character_set(my, "utf8");
            char sql[1024] = {0};

            snprintf(sql, sizeof(sql),
                     "SELECT q.number, q.title, q.star, s.status, s.submit_time " // 新增submit_time字段
                     "FROM oj_submissions s "
                     "JOIN oj_questions q ON s.question_id = q.number "
                     "WHERE s.user_id = '%s'"
                     "ORDER BY s.submit_time DESC;",
                     user_id.c_str());

            if (mysql_query(my, sql) == 0)
            {
                MYSQL_RES *res = mysql_store_result(my);
                if (res)
                {
                    MYSQL_ROW row;
                    while ((row = mysql_fetch_row(res)))
                    {
                        Submission sub;
                        sub.number = row[0];
                        sub.title = row[1];
                        sub.star = row[2];
                        sub.status = row[3];
                        sub.submit_time = row[4]; 
                        submissions.push_back(sub);
                    }
                    mysql_free_result(res);
                }
                else
                {
                    cout << "失败" << endl;
                }
            }
            else
            {
                cout << "失败" << endl;
            }
            mysql_close(my);
            return submissions;
        }

        // 获取用户ID
        int GetUserId(const string &username)
        {
            MYSQL *my = Connect();
            if (my == nullptr)
            {
                LOG(ERROR) << "连接数据库失败" << endl;
                return -1;
            }

            string sql = "SELECT id FROM users WHERE username = '" + username + "'";
            
            if (mysql_query(my, sql.c_str()) != 0)
            {
                LOG(ERROR) << "查询用户ID失败: " << mysql_error(my) << endl;
                mysql_close(my);
                return -1;
            }

            MYSQL_RES *res = mysql_store_result(my);
            if (res == nullptr || mysql_num_rows(res) != 1)
            {
                mysql_close(my);
                return -1;
            }

            MYSQL_ROW row = mysql_fetch_row(res);
            int user_id = atoi(row[0]);

            mysql_free_result(res);
            mysql_close(my);
            return user_id;
        }
    };
}